package dao;

import entity.ShoppingList;
import entity.ShoppingListItem;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.json.JSONArray;
import org.json.JSONObject;
import utils.DateUtils;
import utils.DbUtils;

import java.sql.SQLException;
import java.util.List;

/**
 * @author lishunlin110
 * @version 1.0
 * @description: 购物清单交互层接口实现
 * @date 2021/10/16 21:44
 */
public class ShoppingListDaoImpl implements ShoppingListDao{

    QueryRunner queryRunner = new QueryRunner(DbUtils.getDateSource());
    @Override
    public List<ShoppingList> getShoppingList(String userId) {
        String sql = "select* from shopping_list where u_id=?";
        try {
            return  queryRunner.query(sql, new BeanListHandler<>(ShoppingList.class), userId);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<ShoppingListItem> getShoppingListItem(int shoppinglistId) {
        String sql = "select * from shopping_list_item where shoppinglist_id=?";
        try {
            return  queryRunner.query(sql,new BeanListHandler<>(ShoppingListItem.class),shoppinglistId);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public int deleteShoppingList(int id) {
        String sql="delete from shopping_list where id=?";
        try {
            return queryRunner.update(sql,id);
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int addShoppingList(ShoppingList shoppingList ) {
        String sql="insert into shopping_list(u_id,create_time) values(?,?)";
        Object[] params={shoppingList.getU_id(), DateUtils.utilToSql(shoppingList.getCreate_time())};
        try {
            return queryRunner.update(sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int addShoppingListItem(ShoppingListItem shoppingListItem) {
        String sql="insert into shopping_list_item(shoppinglist_id,p_num,color,price,count,type,pic_adr) values(?,?,?,?,?,?,?)";
        Object[] params={shoppingListItem.getShoppinglist_id(),shoppingListItem.getP_num(),
                shoppingListItem.getColor(),shoppingListItem.getPrice(),
                shoppingListItem.getCount(),shoppingListItem.getType(),shoppingListItem.getPic_adr()};
        try {
            return queryRunner.update(sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int getShoppingListId(String u_id) {
        String sql = "select id from shopping_list where u_id=? order by id desc ";
        try {
            Long l =  queryRunner.query(sql, new ScalarHandler<>("id"),u_id);
            return l.intValue();
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
    }
}
